﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using iAnywhere.Data.SQLAnywhere;
using System.Configuration;
using Wicresoft.UnifyShow.Utility;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class Alert : Basic, IAlert
    {
        private const string selectAlertBySiteBSAndDeviceID = "select a.*,d.identify as host from alert a inner join device d on a.DeviceID=d.id " +
"where a.status='OPEN' and d.siteid=:siteid and d.businesssystemid=:businesssystemid";
        private const string selectAlertBySiteAndDeviceID = "select a.*,d.identify as host from alert a inner join device d on a.DeviceID=d.id " +
"where  a.status='OPEN' and  d.siteid=:siteid ";


        //获取今日新增告警
        private const string sqlCreateAlertCreateInToday = "select identity(10) as rownumber,MC_HOST_ADDRESS as Identify,MC_ORIGIN_CLASS as Type, MSG, dateadd(hh,8,dateadd(second, MC_ARRIVaL_TIME, date('1970-01-01'))) as CreateTime, case when status='OPEN' then null when status='CLOSED' then dateadd(hh,8,dateadd(second, CLOSED_TIME, date('1970-01-01'))) end as LastModifiedTime, status into #tempEvent_today from dba.eg_event_view where MC_OWNER='福建省烟草专卖局' and SEVERITY='CRITICAL' and createtime >= CONVERT(varchar(8),getdate(),112) and createtime < CONVERT(varchar(8),dateadd(day,1,getdate()),112) {0}";
        private const string sqlGetAlertCreateInToday = "select * from #tempEvent_today where rownumber >= {0} and rownumber <= {1}";
        private const string sqlGetAlertCreateInTodayCount = "select count(*) from #tempEvent_today";
        private const string sqlDropAlertInToday = "BEGIN DROP TABLE #tempEvent_today;EXCEPTION WHEN OTHERS THEN END";

        //获取未关闭告警
        private const string sqlCreateAlertNotClosed = "select identity(10) as rownumber,MC_HOST_ADDRESS as Identify,MC_ORIGIN_CLASS as Type, MSG, dateadd(hh,8,dateadd(second, MC_ARRIVaL_TIME, date('1970-01-01'))) as CreateTime, case when status='OPEN' then null when status='CLOSED' then dateadd(hh,8,dateadd(second, CLOSED_TIME, date('1970-01-01'))) end as LastModifiedTime, status into #tempEvent_open from dba.eg_event_view where MC_OWNER='福建省烟草专卖局' and status='OPEN' and SEVERITY='CRITICAL' and createtime < CONVERT(varchar(8),getdate(),112) {0}";
        private const string sqlGetNotClosedAlert = "select * from #tempEvent_open where rownumber >= {0} and rownumber <= {1}";
        private const string sqlGetNotClosedAlertCount = "select count(*) from #tempEvent_open";
        private const string sqlDropAlertNotClosed = "BEGIN DROP TABLE #tempEvent_open;EXCEPTION WHEN OTHERS THEN END";

        public List<AlertDetailAndDevice> GetAlertBySiteBSAndDeviceID(int siteID, int bsID, int? deviceID, out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "Timestamp", string sortDirection = "Descending"/*Ascending,Descending*/)
        {
            string sql = selectAlertBySiteBSAndDeviceID;
            List<OracleParameter> paramss = new List<OracleParameter>()
                {
                    new OracleParameter(){ ParameterName="siteid",OracleDbType=OracleDbType.Decimal,Value=siteID},
                     new OracleParameter(){ ParameterName="businesssystemid",OracleDbType=OracleDbType.Decimal,Value=bsID}
                };
            if (deviceID.HasValue)
            {
                sql += " and d.id=:id";
                paramss.Add(
                    new OracleParameter() { ParameterName = "id", OracleDbType = OracleDbType.Decimal, Value = deviceID }
                    );
            }
            var copy = new List<OracleParameter>();
            paramss.ForEach(item => copy.Add((OracleParameter)(item.Clone())));
            totalRecords = ModelHelper<AlertModel>.GetModelCount_By_DirectSql(SQLHelper.GetCountSQLFormDirectSQL(sql), paramss, oda);
            return ModelHelper<AlertDetailAndDevice>.GetModel_By_DirectSql(SQLHelper.GetPageSQLFormDirectSQL(sql, sortName, sortDirection,
                pageSize, pageIndex), copy, oda);
        }

        public List<AlertDetailAndDevice> GetAlertBySiteAndDeviceID(int? siteID, int? deviceID, out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "Timestamp", string sortDirection = "Descending"/*Ascending,Descending*/)
        {
            string sql = selectAlertBySiteAndDeviceID;
            List<OracleParameter> paramss = new List<OracleParameter>();
            if (deviceID.HasValue)
            {
                sql = "select a.*,d.identify as host from alert a inner join device d on a.DeviceID=d.id " +
"where  a.status='OPEN' and d.id=:id";
                paramss.Add(
                    new OracleParameter() { ParameterName = "id", OracleDbType = OracleDbType.Decimal, Value = deviceID }
                    );
            }
            else
            {
                if (siteID.HasValue)
                {
                    paramss.Add(
                        new OracleParameter() { ParameterName = "siteid", OracleDbType = OracleDbType.Decimal, Value = siteID });
                }
                else
                {
                    sql = "select a.*,d.identify as host from alert a inner join device d on a.DeviceID=d.id " +
"where  a.status='OPEN'";
                }
            }
            var copy = new List<OracleParameter>();
            paramss.ForEach(item => copy.Add((OracleParameter)(item.Clone())));
            totalRecords = ModelHelper<AlertModel>.GetModelCount_By_DirectSql(SQLHelper.GetCountSQLFormDirectSQL(sql), paramss, oda);
            return ModelHelper<AlertDetailAndDevice>.GetModel_By_DirectSql(SQLHelper.GetPageSQLFormDirectSQL(sql, sortName, sortDirection,
                pageSize, pageIndex), copy, oda);
        }

        public List<AlertModel> GetOpenAlertBySiteID(int site, DateTime? sTime, DateTime? eTime, int? ps, int? pi)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = string.Empty;
            string whereStr = string.Empty;

            if (site != null)
            {
                whereStr += " and deviceid in (select id from device where siteid=:siteid) ";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }

            if (sTime != null)
            {
                whereStr += " and timestamp >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                whereStr += " and timestamp <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }

            if (ps.HasValue && pi.HasValue)
            {
                int beginIndex = pi.Value * ps.Value;
                int endIndex = (pi.Value + 1) * ps.Value;
                sqlStr = "select * from ( select alert.*,row_number() over ( order by timestamp desc) rn from alert where status =  '" + Alert_Satus.Open + "'" + whereStr + " ) where rn between " + beginIndex + " and " + endIndex;
            }
            else
            {
                sqlStr = "select * from alert where status =  '" + Alert_Satus.Open + "'" + whereStr;
            }
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<AlertModel>.GetModel_By_OracleDataReader(dr);
        }

        /// <summary>
        /// 获取今日新产生的告警
        /// </summary>
        /// <param name="siteID"></param>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<AlertDetailAndDevice> GetAlertCreateInToday(int? siteID, out decimal count, int pageIndex, int pageSize, string sort = "CreateTime", string order = "Descending")
        {
            var list = new List<AlertDetailAndDevice>();

            int beginIndex = (pageIndex - 1) * pageSize + 1;
            int endIndex = beginIndex + pageSize - 1;
            count = 0;

            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }

            var sql_create = string.Format(sqlCreateAlertCreateInToday, string.Format("order by {0} {1}", sort, orderName));
            var sql_select = string.Format(sqlGetAlertCreateInToday, beginIndex, endIndex);

            using (SAConnection BPPMdb = new SAConnection(ConfigurationManager.AppSettings["BPPMDB"]))
            {
                BPPMdb.Open();

                try
                {
                    using (SACommand cmd_create = new SACommand(sql_create, BPPMdb))
                    {
                        cmd_create.ExecuteNonQuery();
                    }

                    using (var selectCommand = new SACommand(sql_select, BPPMdb))
                    {
                        var selectResult = selectCommand.ExecuteReader();
                        while (selectResult.Read())
                        {
                            list.Add(new AlertDetailAndDevice()
                            {
                                Identify = selectResult["Identify"].ToString(),
                                Type = selectResult["Type"].ToString(),
                                Msg = selectResult["MSG"].ToString(),
                                CreateTime = selectResult["CreateTime"].ToString().ToDateTime(),
                                LastModifiedTime = selectResult["LastModifiedTime"].ToString().ToDateTime()

                            });
                        }
                        selectResult.Close();
                        count = (int)new SACommand(sqlGetAlertCreateInTodayCount, BPPMdb).ExecuteScalar();
                    }

                }
                finally
                {
                    new SACommand(sqlDropAlertInToday, BPPMdb).ExecuteScalar();
                }

                BPPMdb.Close();

                return list;
            }



        }

        /// <summary>
        /// 获取今天之前产生并且未关闭的告警
        /// </summary>
        /// <param name="siteID"></param>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<AlertDetailAndDevice> GetNoClosedAlert(int? siteID, out decimal count, int pageIndex, int pageSize, string sort = "CreateTime", string order = "Descending")
        {
            var list = new List<AlertDetailAndDevice>();
            int beginIndex = (pageIndex - 1) * pageSize + 1;
            int endIndex = beginIndex + pageSize - 1;

            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }

            var sql_create = string.Format(sqlCreateAlertNotClosed, string.Format("order by {0} {1}", sort, orderName));
            var sql_select = string.Format(sqlGetNotClosedAlert, beginIndex, endIndex);

            using (SAConnection BPPMdb = new SAConnection(ConfigurationManager.AppSettings["BPPMDB"]))
            {
                BPPMdb.Open();

                try
                {
                    using (SACommand cmd_create = new SACommand(sql_create, BPPMdb))
                    {
                        cmd_create.ExecuteNonQuery();
                    }

                    using (var selectCommand = new SACommand(sql_select, BPPMdb))
                    {
                        var selectResult = selectCommand.ExecuteReader();
                        while (selectResult.Read())
                        {
                            list.Add(new AlertDetailAndDevice()
                            {
                                Identify = selectResult["Identify"].ToString(),
                                Type = selectResult["Type"].ToString(),
                                Msg = selectResult["MSG"].ToString(),
                                CreateTime = selectResult["CreateTime"].ToString().ToDateTime(),
                                LastModifiedTime = selectResult["LastModifiedTime"].ToString().ToDateTime()
                            });
                        }
                        selectResult.Close();
                        count = (int)new SACommand(sqlGetNotClosedAlertCount, BPPMdb).ExecuteScalar();
                    }
                }
                finally
                {
                    new SACommand(sqlDropAlertNotClosed, BPPMdb).ExecuteScalar();
                }

                BPPMdb.Close();

                return list;

            }

        }

        public bool HasIncludePendingAlert()
        {
            var result = oda.ExecuteScalar("select  includependingalert from systemtopology where rownum=1", null);
            //DataTable result = oda.ExecuteFillToDataTable("select  includependingalert from systemtopology where rownum=1");

            return result != null && Int32.Parse(result.ToString()) == 1;
        }


        public bool UpdateIncludePendingAlert(decimal value)
        {
            var result = oda.ExecuteCommand("update systemtopology set includependingalert = :isInclude",
                new List<OracleParameter>() { 
                    new OracleParameter(){
                        ParameterName="isInclude",
                            OracleDbType = OracleDbType.Decimal,
                            Value = value
                    }
            });

            return result >= 1;
        }
    }
}
